﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using PM.Entity;
using PM.Common;
using System.Data.OracleClient;
using System.Data;
using System.Windows.Forms;

namespace PM.Services
{
    public static class ClientServices
    {
        private static Client GetClientFromDataReader(IDataReader dataReader)
        {
            Client retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new Client();
                retVal.ClientId = dataReader["CLIENT_ID"].ToString();
                retVal.CDS = dataReader["CDS_NO"].ToString();
                retVal.Title = dataReader["TITLE"].ToString();
                retVal.Initials = dataReader["INITIALS"].ToString();
                retVal.Other_Names = dataReader["OTHER_NAMES"].ToString();
                retVal.FamilyName = dataReader["FAMILY_NAME"].ToString();
                retVal.Surname = dataReader["SURNAME"].ToString();
                DateTime dob = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["DOB"].ToString(), out dob))
                {
                    retVal.DoB = dob;
                }

                retVal.Gender = dataReader["GENDER"].ToString();
                retVal.MaritalStatus = dataReader["MARITAL_STATUS"].ToString();
                int noOfJoints = 0;
                int.TryParse(dataReader["NO_OF_JOINT_INVS"].ToString(), out noOfJoints);
                retVal.NoOfJointInvestors = noOfJoints;

                retVal.AddressRes = dataReader["ADDRESS_RES"].ToString();
                retVal.AddressOff = dataReader["ADDRESS_OFF"].ToString();
                retVal.TeleMob = dataReader["TELE_MOB"].ToString();
                retVal.TeleRes = dataReader["TELE_RES"].ToString();
                retVal.TeleOff = dataReader["TELE_OFF"].ToString();
                DateTime regDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["REG_DATE"].ToString(), out regDate))
                {
                    retVal.RegDate = regDate;                  
                    
                }

                retVal.Fax = dataReader["FAX"].ToString();
                retVal.Email = dataReader["EMAIL"].ToString();
                retVal.SpouseId = dataReader["SPOUSE_ID"].ToString();
                retVal.AdvisorId = dataReader["ADVISOR_ID"].ToString();
                retVal.Occupation = dataReader["OCCUPATION"].ToString();
                retVal.Remarks = dataReader["REMARKS"].ToString();
                retVal.CountryOfRes = dataReader["COUNTRY_RES"].ToString();
                retVal.Citizenship = dataReader["CITIZENSHIP"].ToString();
                retVal.BankAccNo = dataReader["BANK_ACC_NO"].ToString();

                int active = 0;
                int.TryParse(dataReader["ACTIVE"].ToString(), out active);
                if (active == 1)
                {
                    retVal.Active = true;
                }
                else
                {
                    retVal.Active = false;
                }

            }
            return retVal;
        }

        public static Client GetClientByID(string client_id)
        {
            Client retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from CLIENT where CLIENT_ID = '" + client_id + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetClientFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static Client GetClientByCDSId(string cdsId)
        {
            Client retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from CLIENT where CDS_NO = '" + cdsId + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetClientFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static ClientCollection GetClientCollection()
        {
            ClientCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from client";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new ClientCollection();
                    while (dataReader.Read())
                    {
                        Client item = GetClientFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        //public static List<string> GetClientCodeCollection(string agent)
        //{
        //    List<string> retVal = null;
        //    OracleConnection con = new OracleConnection();

        //    try
        //    {
        //        con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        //        con.Open();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.CommandText = "select CODE from client where agent = '" + agent + "'";
        //        cmd.Connection = con;

        //        using (IDataReader dataReader = cmd.ExecuteReader())
        //        {
        //            retVal = new List<string>();
        //            while (dataReader.Read())
        //            {
        //                string item = dataReader["CODE"].ToString();
        //                if (!string.IsNullOrEmpty(item))
        //                    retVal.Add(item);
        //            }
        //        }
        //    }
        //    catch (System.Exception exc)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //    return retVal;
        //}

        public static List<string> GetClientIDCollection()
        {
            List<string> retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select client_id from client";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new List<string>();
                    while (dataReader.Read())
                    {
                        string item = dataReader["CLIENT_ID"].ToString();
                        if (!string.IsNullOrEmpty(item))
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static ClientCollection GetClientID_Name_Collection()
        {
            ClientCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select t.client_id, t.client_id || ' - ' || t.title || '.' || t.other_names || ' ' || t.surname " +
                                  "as  CLIENTNAME FROM client t";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new ClientCollection();

                    while (dataReader.Read())
                    {

                        Client client = new Client();
                        client.ClientId = dataReader["CLIENT_ID"].ToString();
                        client.Other_Names = dataReader["CLIENTNAME"].ToString();
                        if (!string.IsNullOrEmpty(client.ClientId))
                        {
                            retVal.Add(client);
                        }

                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetClientCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from client";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static AutoCompleteStringCollection GetClientIDACCollection()
        {
            AutoCompleteStringCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT client_id FROM CLIENT";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new AutoCompleteStringCollection();
                    while (dataReader.Read())
                    {
                        string item = dataReader["CLIENT_ID"].ToString();
                        if (!string.IsNullOrEmpty(item))
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static AutoCompleteStringCollection GetClientNameCollection()
        {
            AutoCompleteStringCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT CLIENT.SURNAME || ' ' || CLIENT.INITIALS || ' (' || CLIENT.CLIENT_ID || ')'" +
                                  "as  CLIENTNAME FROM CLIENT";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new AutoCompleteStringCollection();
                    while (dataReader.Read())
                    {
                        string item = dataReader["CLIENTNAME"].ToString();
                        if (!string.IsNullOrEmpty(item))
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        /* public static List<string> GetClientIDCollection(string agent)
         {
             List<string> retVal = null;
             OracleConnection con = new OracleConnection();

             try
             {
                 con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                 con.Open();
                 OracleCommand cmd = new OracleCommand();
                 cmd.CommandText = "select Client_ID from client where agent = '" + agent + "'";
                 cmd.Connection = con;

                 using (IDataReader dataReader = cmd.ExecuteReader())
                 {
                     retVal = new List<string>();
                     while (dataReader.Read())
                     {
                         string item = dataReader["CODE"].ToString();
                         if (!string.IsNullOrEmpty(item))
                             retVal.Add(item);
                     }
                 }
             }
             catch (System.Exception exc)
             {
                 throw;
             }
             finally
             {
                 con.Close();
             }
             return retVal;
         }
         */

        public static void Add(Client client)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                //string client_temp_id = String.
                // cmd.CommandText = "select 1 from Client where CLIENT_ID = '" + client.ClientId + "'";
                //cmd.Connection = con;

                // cmd.ExecuteNonQuery();
                // cmd.

                cmd.CommandText = "insert into client(CLIENT_ID, TITLE, INITIALS, OTHER_NAMES, SURNAME, DOB, GENDER, MARITAL_STATUS, NO_OF_JOINT_INVS, ADDRESS_RES," +
                                  "ADDRESS_OFF, TELE_MOB, TELE_RES, TELE_OFF, EMAIL, FAX, OCCUPATION, SPOUSE_ID, ADVISOR_ID, REG_DATE, REMARKS, ACTIVE, CDS_NO," +
                                  "FAMILY_NAME, COUNTRY_RES, CITIZENSHIP, BANK_ACC_NO )";


                cmd.CommandText += "values(:v1,:v2,:v3,:v4,:v5,:v6,:v7,:v8,:v9,:v10,:v11,:v12,:v13,:v14,:v15,:v16,:v17,:v18,:v19,:v20,:v21,:v22,:v23,:v24,:v25,:v26,:v27)";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = client.ClientId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = client.Title;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = client.Initials;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = client.Other_Names;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = client.Surname;
                cmd.Parameters.Add(":v6", OracleType.DateTime).Value = client.DoB;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = client.Gender;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = client.MaritalStatus;
                cmd.Parameters.Add(":v9", OracleType.Number).Value = client.NoOfJointInvestors;
                cmd.Parameters.Add(":v10", OracleType.VarChar).Value = client.AddressRes;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = client.AddressOff;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = client.TeleMob;
                cmd.Parameters.Add(":v13", OracleType.VarChar).Value = client.TeleRes;
                cmd.Parameters.Add(":v14", OracleType.VarChar).Value = client.TeleOff;
                cmd.Parameters.Add(":v15", OracleType.VarChar).Value = client.Email;
                cmd.Parameters.Add(":v16", OracleType.VarChar).Value = client.Fax;
                cmd.Parameters.Add(":v17", OracleType.VarChar).Value = client.Occupation;
                cmd.Parameters.Add(":v18", OracleType.VarChar).Value = client.SpouseId;
                cmd.Parameters.Add(":v19", OracleType.VarChar).Value = client.AdvisorId;
                cmd.Parameters.Add(":v20", OracleType.DateTime).Value = client.RegDate;
                cmd.Parameters.Add(":v21", OracleType.VarChar).Value = client.Remarks;
                if (client.Active)
                {
                    cmd.Parameters.Add(":v22", OracleType.Number).Value = 1;
                }
                else
                {
                    cmd.Parameters.Add(":v22", OracleType.Number).Value = 0;
                }
                cmd.Parameters.Add(":v23", OracleType.VarChar).Value = client.CDS;
                cmd.Parameters.Add(":v24", OracleType.VarChar).Value = client.FamilyName;
                cmd.Parameters.Add(":v25", OracleType.VarChar).Value = client.CountryOfRes;
                cmd.Parameters.Add(":v26", OracleType.VarChar).Value = client.Citizenship;
                cmd.Parameters.Add(":v27", OracleType.VarChar).Value = client.BankAccNo;

                cmd.Connection = con;

                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(string client_id)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "delete from client where client_id = '" + client_id + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Client client)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update client set " +
                                  "TITLE = :v2, " +
                                  "INITIALS = :v3, " +
                                  "OTHER_NAMES = :v4, " +
                                  "SURNAME = :v5, " +
                                  "DOB = :v6, " +
                                  "GENDER = :v7, " +
                                  "MARITAL_STATUS = :v8, " +
                                  "ADDRESS_RES = :v9, " +
                                  "ADDRESS_OFF = :v10, " +
                                  "TELE_MOB = :v11, " +
                                  "TELE_RES = :v12, " +
                                  "TELE_OFF = :v13, " +
                                  "EMAIL = :v14, " +
                                  "FAX = :v15, " +
                                  "OCCUPATION = :v16, " +
                                  "SPOUSE_ID = :v17, " +
                                  "ADVISOR_ID = :v18, " +
                                  "REMARKS = :v19, " +
                                  "REG_DATE = :v20, " +
                                  "ACTIVE = :v21, " +
                                  "NO_OF_JOINT_INVS = :v22, " +
                                  "CDS_NO = :v23, " +
                                  "FAMILY_NAME = :v24, " +
                                  "COUNTRY_RES = :v25, " +
                                  "CITIZENSHIP = :v26, " +
                                  "BANK_ACC_NO = :v27 ";


                cmd.CommandText += " where CLIENT_ID = :v1";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = client.ClientId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = client.Title;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = client.Initials;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = client.Other_Names;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = client.Surname;
                cmd.Parameters.Add(":v6", OracleType.DateTime).Value = client.DoB;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = client.Gender;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = client.MaritalStatus;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = client.AddressRes;
                cmd.Parameters.Add(":v10", OracleType.VarChar).Value = client.AddressOff;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = client.TeleMob;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = client.TeleRes;
                cmd.Parameters.Add(":v13", OracleType.VarChar).Value = client.TeleOff;
                cmd.Parameters.Add(":v14", OracleType.VarChar).Value = client.Email;
                cmd.Parameters.Add(":v15", OracleType.VarChar).Value = client.Fax;
                cmd.Parameters.Add(":v16", OracleType.VarChar).Value = client.Occupation;
                cmd.Parameters.Add(":v17", OracleType.VarChar).Value = client.SpouseId;
                cmd.Parameters.Add(":v18", OracleType.VarChar).Value = client.AdvisorId;
                cmd.Parameters.Add(":v19", OracleType.VarChar).Value = client.Remarks;
                cmd.Parameters.Add(":v20", OracleType.DateTime).Value = client.RegDate;
                if (client.Active)
                {
                    cmd.Parameters.Add(":v21", OracleType.Number).Value = 1;
                }
                else
                {
                    cmd.Parameters.Add(":v21", OracleType.Number).Value = 0;
                }

                cmd.Parameters.Add(":v22", OracleType.Number).Value = client.NoOfJointInvestors;
                cmd.Parameters.Add(":v23", OracleType.VarChar).Value = client.CDS;
                cmd.Parameters.Add(":v24", OracleType.VarChar).Value = client.FamilyName;
                cmd.Parameters.Add(":v25", OracleType.VarChar).Value = client.CountryOfRes;
                cmd.Parameters.Add(":v26", OracleType.VarChar).Value = client.Citizenship;
                cmd.Parameters.Add(":v27", OracleType.VarChar).Value = client.BankAccNo;
                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }



        public static void UpdateCollection(ClientCollection updated_list)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update client set " +
                                  "ACTIVE = :v21 ";
                cmd.CommandText += " where CLIENT_ID = :v1";

                con.Open();
                foreach (Client client in updated_list)
                {


                    cmd.Parameters.Add(":v1", OracleType.VarChar).Value = client.ClientId;
                    if (client.Active)
                    {
                        cmd.Parameters.Add(":v21", OracleType.Number).Value = 1;
                    }
                    else
                    {
                        cmd.Parameters.Add(":v21", OracleType.Number).Value = 0;
                    }
                    cmd.Connection = con;
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
